package com.zx.jhpt.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 描述：Excel写操作帮助类
 * @author	ALEX
 * @since	2010-11-24
 * @version	1.0v
 */
public class ExcelUtil {
	private static final Logger log=Logger.getLogger(ExcelUtil.class);
	
	/**
	 * 总行数
	 */
    private int totalRows = 0;
    
    /**
     * 总列数
     */
    private int totalCells = 0;
    
    /**
	 * 得到总行数
	 * @return
	 */
	public int getTotalRows() {
		return totalRows;
	}
    
    /**
     * 得到总列数
     * @return
     */
	public int getTotalCells() {
		return totalCells;
	}
    
	/**
	 * 功能：将HSSFWorkbook写入Excel文件
	 * @param 	wb		HSSFWorkbook
	 * @param 	absPath	写入文件的相对路径
	 * @param 	wbName	文件名
	 */
	public static void writeWorkbook(HSSFWorkbook wb,String fileName){
		FileOutputStream fos=null;
		try {
			fos=new FileOutputStream(fileName);
			wb.write(fos);
		} catch (FileNotFoundException e) {
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		} catch (IOException e) {
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		} finally{
			try {
				if(fos!=null){
					fos.close();
				}
			} catch (IOException e) {
				log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
			}
		}
	}
	/**
	 * 功能：创建HSSFSheet工作簿
	 * @param 	wb	HSSFWorkbook
	 * @param 	sheetName	String
	 * @return	HSSFSheet
	 */
	public static HSSFSheet createSheet(HSSFWorkbook wb,String sheetName){
		HSSFSheet sheet=wb.createSheet(sheetName);
		sheet.setDefaultColumnWidth(12);
		sheet.setGridsPrinted(false);
		sheet.setDisplayGridlines(false);
		return sheet;
	}
	/**
	 * 功能：创建HSSFRow
	 * @param 	sheet	HSSFSheet
	 * @param 	rowNum	int
	 * @param 	height	int
	 * @return	HSSFRow
	 */
	public static HSSFRow createRow(HSSFSheet sheet,int rowNum,int height){
		HSSFRow row=sheet.createRow(rowNum);
		row.setHeight((short)height);
		return row;
	}
	public static HSSFRow createRow(HSSFSheet sheet,int rowNum){
		HSSFRow row=sheet.createRow(rowNum);
		return row;
	}
	/**
	 * 功能：创建CellStyle样式
	 * @param 	wb				HSSFWorkbook	
	 * @param 	backgroundColor	背景色	
	 * @param 	foregroundColor	前置色
	 * @param	font			字体
	 * @return	CellStyle
	 */
	public static CellStyle createCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
		CellStyle cs=wb.createCellStyle();
		cs.setAlignment(halign);
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setFillBackgroundColor(backgroundColor);
		cs.setFillForegroundColor(foregroundColor);
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(font);
		return cs;
	}
	/**
	 * 功能：创建带边框的CellStyle样式
	 * @param 	wb				HSSFWorkbook	
	 * @param 	backgroundColor	背景色	
	 * @param 	foregroundColor	前置色
	 * @param	font			字体
	 * @return	CellStyle
	 */
	public static CellStyle createBorderCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
		CellStyle cs=wb.createCellStyle();
		cs.setAlignment(halign);
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setFillBackgroundColor(backgroundColor);
		cs.setFillForegroundColor(foregroundColor);
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(font);
		cs.setBorderLeft(CellStyle.BORDER_DASHED);
		cs.setBorderRight(CellStyle.BORDER_DASHED);
		cs.setBorderTop(CellStyle.BORDER_DASHED);
		cs.setBorderBottom(CellStyle.BORDER_DASHED);  
		return cs;
	}
	/**
	 * 功能：创建CELL
	 * @param 	row		HSSFRow	
	 * @param 	cellNum	int
	 * @param 	style	HSSFStyle
	 * @return	HSSFCell
	 */
	public static HSSFCell createCell(HSSFRow row,int cellNum,CellStyle style){
		HSSFCell cell=row.createCell(cellNum);
		if(style!=null){
			cell.setCellStyle(style);
		}		
		return cell;
	}
	/**
	 * 功能：合并单元格
	 * @param 	sheet		HSSFSheet
	 * @param 	firstRow	int
	 * @param 	lastRow		int
	 * @param 	firstColumn	int
	 * @param 	lastColumn	int
	 * @return	int			合并区域号码
	 */
	public static int mergeCell(HSSFSheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){
		return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn));	
	}
	/**
	 * 功能：创建字体
	 * @param 	wb			HSSFWorkbook	
	 * @param 	boldweight	short
	 * @param 	color		short
	 * @return	Font	
	 */
	public static Font createFont(HSSFWorkbook wb,short boldweight,short color,short size){
		Font font=wb.createFont();
		font.setBoldweight(boldweight);
		font.setColor(color);
		font.setFontHeightInPoints(size);
		return font;
	}
	/**
	 * 设置合并单元格的边框样式
	 * @param	sheet	HSSFSheet	
	 * @param 	ca		CellRangAddress
	 * @param 	style	CellStyle
	 */
	public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress ca,CellStyle style) {  
	    for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {  
	        HSSFRow row = HSSFCellUtil.getRow(i, sheet);  
	        for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {  
	            HSSFCell cell = HSSFCellUtil.getCell(row, j);  
	            cell.setCellStyle(style);  
	        }  
	    }  
	} 
	/**
	 * 获取单元格对象
	 * @param sheet
	 * @param rowIndex
	 * @param cellIndex
	 * @return
	 */
	public static HSSFCell getCell(HSSFSheet sheet, int rowIndex, int cellIndex) {
		HSSFRow row = sheet.getRow(rowIndex);
		if(row==null){
			row=sheet.createRow(rowIndex);
		}
		HSSFCell cell= row.getCell(cellIndex);
		if(cell==null){
			cell=row.createCell(cellIndex);
		}
		return cell;
	}
	public static HSSFCell setCellValue(HSSFSheet sheet, int rowIndex, int colIndex,Object value,int typeValue){
		HSSFCell cell = getCell(sheet, rowIndex, colIndex);
		if (null!=value) {
			switch (typeValue) {
			case 0:
				cell.setCellValue(Double.parseDouble(value.toString()));
				break;
			case 1:
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(value.toString());
				break;
			case 2:
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(Long.valueOf(value.toString()));
				break;
			case 3:
				cell.setCellValue((Date)value);
				break;
			default:
				cell.setCellValue(value.toString());
				break;
			}
		}else{
			cell.setCellValue("");
		}
		return cell;
	}
	public static HSSFCell setCellValue(HSSFSheet sheet, int rowIndex, int colIndex,Object value,int typeValue,CellStyle style){
		HSSFCell cell = ExcelUtil.setCellValue(sheet, rowIndex, colIndex, value, typeValue);
		if (null != style) {
			cell.setCellStyle(style);
		}
		return cell;
	}
	
	public static HSSFCell setCellValue(HSSFSheet sheet, int rowIndex, int colIndex,Object value,int typeValue,CellStyle style,Font font){
		HSSFCell cell = ExcelUtil.setCellValue(sheet, rowIndex, colIndex, value, typeValue);
		if (null != style) {
			style.setFont(font);
			cell.setCellStyle(style);
		}else{
			style = cell.getCellStyle();
			if (null == style) {
				style = sheet.getWorkbook().createCellStyle();
				style.setFont(font);
				cell.setCellStyle(style);
			}else{
				style.setFont(font);
				cell.setCellStyle(style);
			}
		}
		return cell;
	}
	
	/**
	 * 根据文件名读取excel文件
	 * @param file
	 * @param fileName
	 * @return
	 */
	public List<ArrayList<Object>> read(File file, String fileName) {
		List<ArrayList<Object>> dataLst = new ArrayList<ArrayList<Object>>();

		// 检查文件名是否为空或者是否是Excel格式的文件
		if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
			return dataLst;
		}

		boolean isExcel2003 = true;
		// 对文件的合法性进行验证
		if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}

		// 检查文件是否存在
		if (file == null || !file.exists()) {
			return dataLst;
		}

		try {
			// 调用本类提供的根据流读取的方法
			dataLst = read(new FileInputStream(file), isExcel2003);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		// 返回最后读取的结果
		return dataLst;
	}
    
	/**
	 * 根据流读取Excel文件]
	 * 
	 * @param inputStream
	 * @param isExcel2003
	 * @return
	 */
	public List<ArrayList<Object>> read(InputStream inputStream,
			boolean isExcel2003) {
		List<ArrayList<Object>> dataLst = null;
		try {
			// 根据版本选择创建Workbook的方式
			Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream)
					: new XSSFWorkbook(inputStream);
			dataLst = read(wb);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		return dataLst;
	}
    
    /**
     * 读取数据
     * @param wb
     * @return
     */
	private List<ArrayList<Object>> read(Workbook wb) {
		List<ArrayList<Object>> dataLst = new ArrayList<ArrayList<Object>>();

		// 得到第一个shell
		Sheet sheet = wb.getSheetAt(0);
		this.totalRows = sheet.getPhysicalNumberOfRows();
		if (this.totalRows >= 1 && sheet.getRow(0) != null) {
			this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}

		// 循环Excel的行
		for (int r = 0; r < this.totalRows; r++) {
			Row row = sheet.getRow(r);
			if (row == null) {
				continue;
			}

			ArrayList<Object> rowLst = new ArrayList<Object>();
			// 循环Excel的列
			for (short c = 0; c < this.getTotalCells(); c++) {
				Cell cell = row.getCell(c);
				Object cellValue = "";
				if (cell == null) {
					rowLst.add(cellValue);
					continue;
				}
				// 处理数字型的,自动去零
				if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					// 在excel里,日期也是数字,在此要进行判断
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						cellValue = cell.getDateCellValue();
					} else {
						cellValue = getRightStr(cell.getNumericCellValue() + "");
					}
				}
				// 处理字符串型
				else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
					cellValue = cell.getStringCellValue();
				}
				// 处理布尔型
				else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
					cellValue = cell.getBooleanCellValue() + "";
				}
				// 其它的,非以上几种数据类型
				else {
					cellValue = cell.toString() + "";
				}
				rowLst.add(cellValue);
			}
			dataLst.add(rowLst);
		}
		return dataLst;
	}
    
    /**
     * 正确地处理整数后自动加零的情况
     * @param sNum
     * @return
     */
	private String getRightStr(String sNum) {
		DecimalFormat decimalFormat = new DecimalFormat("#.000000");
		String resultStr = decimalFormat.format(new Double(sNum));
		if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) {
			resultStr = resultStr.substring(0, resultStr.indexOf("."));
		}
		return resultStr;
	}
	public static void main(String[] args) {
		ExcelUtil excelUtil = new ExcelUtil();
		List<ArrayList<Object>> dataLst = excelUtil.read(new File("D:\\1.xlsx"),"1.xlsx");
        /*String [][]info = new String[dataLst.size()][dataLst.get(0).size()];
        for (int i = 0; i < dataLst.size(); i++) {
			for (int j = 0; j < dataLst.get(i).size(); j++) {
				info[i][j] = dataLst.get(i).get(j);
			}
		}
        for (int i = 0; i < info.length; i++) {
        	StringBuffer rowData = new StringBuffer();
			for (int j = 0; j < info[i].length; j++) {
				rowData.append(",").append(info[i][j]);
			}
			if (rowData.length() > 0)
            {
                System.out.println(rowData.deleteCharAt(0).toString());
            }
		}*/
        for (ArrayList<Object> innerLst : dataLst)
        {
        	System.out.println(innerLst);
            StringBuffer rowData = new StringBuffer();
            for (Object dataStr : innerLst)
            {
                rowData.append(",").append(dataStr);
            }
            if (rowData.length() > 0)
            {
                System.out.println(rowData.deleteCharAt(0).toString());
            }
        }
	}
}